OData endpoint

Introduction

The Assetic OData endpoint allows for retrieval of Assetic data without needing to create a predefined search profile.

In simple terms OData is a standard way for cloud applications to make information available via the internet. Spreadsheet tools such as Microsoft Excel and report tools such as Microsoft Power BI can access the information from Assetic for reporting purposes.

Other tools in the marketplace that support OData include ODBC drivers for OData that allow the data to be accessed from applications that are able to connect to ODBC data sources.

The metadata for the endpoint is returned as XML

The data itself however is returned in JSON format which is a lightweight data-interchange format common for this kind of application.

If you are using a tool like Microsoft Excel or Power BI then there is no need to worry about understanding this data format.

Note: The Assetic OData endpoint is read-only, this means the data cannot be altered, only queried.

Note: The OData version is v4.0

Assetic publishes an OData endpoint for the following modules:

Entity	Module	Description
functionallocations	Functional Locations	Asset Functional Locations
assets	Assets	Assets
component	Components	Asset components
networkmeasure	Components	Asset component network measure
fairvaluation	Components	Asset component fair valuation
servicecriteria	Components	Asset component service criteria score
treatments	Components	Asset component treatment history
documents	Documents	Documents
workorder	Maintenance	Work Order
workrequest	Maintenance	Work Request
workorderresource	Maintenance	Maintenance Resource
workorderserviceactivity	Maintenance	Work Order Service Activity
workordermaterial	Maintenance	Work Order Material

In OData terms each of these modules is known as an 'entity'.

Note: the entity is case sensitive when used in the URL

Note: You must specify the fields to want to be returned via the $select parameter described below (unless using an aggregate or group by search), else the endpoint will not return any data. In other words there is no 'select *' concept that SQL users would be familiar with. Failure to define a $select parameter (unless aggregate or group by request) leads to the following response:

{"@odata.context":"http://[your_env].assetic.net/odata/$metadata#[entity]",
"value":[]}

Where [entity] is the name of the entity in the request

Assetic OData endpoint URL

The URL of the OData endpoint shown below. Replace [your_env] with your Assetic environment name.

https://[your_env].assetic.net/odata

where [your_env] is your base Assetic URL. For example: https://myorganisation.assetic.net/odata (where [your_env] = myorganisation.assetic.net)

The article OData-Excel-and-Power-BI shows how to use the OData endpoint with reporting tools such as Microsoft Excel and Power BI.

Authentication

The OData endpoint uses the same "HTTPS Basic" username and API token authentication as the Assetic REST API's.

If you are already logged into Assetic in a browser window then you can paste into a browser tab the URL's in the examples in this article to see what is returned by the OData endpoint.

If using Excel or Power BI you will need to enter your Assetic credentials as part of the process of setting up a query.

Metadata

The metadata document for the OData endpoint provides a list of the supported Assetic modules and the fields within those modules.

The following URL may be used to get the metadata (note that authentication is not required for the metadata query). Replace [your_env] with your Assetic environment name.

https://[your_env].assetic.net/odata/$metadata

The endpoint is a useful way to get a listing of field names with the corresponding user-friendly label displayed in the Assetic browser application.

The metadata for the endpoint is returned as XML.

Supported OData Operations

In the examples below whitespaces are replaced with %20 to ensure the URL is valid.

Select, Top and Skip

Select is used to define the list of fields to retrieve via the OData endpoint.

Note: You must specify the fields (unless using an aggregate or group by search), else the endpoint will not return any data. In other words, there is no 'select *' concept that SQL users would be familiar with.

Top is used to define the number of records to return, up to the Assetic OData endpoint limit of 10,000 records.

The OData specification outlines the syntax for Select, Top and Skip.

The following sample returns the first 10,000 asset fields listed in the select statement:

https://[your_env].assetic.net/odata/assets?$top=10000&$select=ComplexAssetId,ComplexAssetAssetCategory,ComplexAssetName, ComplexAssetLastModified,CAStatus,CAOCI

If $top is not specified, then the Assetic OData endpoint will by default return the first 5000 records.

Note: Some tools (such as Safe Software FME OData Reader) do not include '$select' in the OData query on the assumption that by omitting '$select', all fields will be returned. This assumption is not supported.

Note: The OData $skip operation is also supported, but since the Assetic OData endpoint will only ever return the first 10,000 results regardless of $top and $skip, $skip cannot be used to skip the first 10,000 records to expose the next 10,000 records.

Filter

The above search may be further refined using a filter to target a specific set of records.

The syntax is $filter=[fieldname] [Operator] '[filtervalue]'

Substitute the parameters in square braces with the actual values.

Supported filter operators are:

Operator	Description	Example
eq	Equals	$filter=ComplexAssetName eq 'Smith Road'
ne	Not Equal To	$filter=ComplexAssetName ne 'Smith Road'
gt	Greater Than	$filter=CAOCI gt 2
ge	Greater Than or Equal To	$filter=CAOCI ge 2
lt	Less Than	$filter=CAOCI lt 2
le	Less Than or Equal To	$filter=CAOCI le 2
and	And	$filter=CAOCI lt 2 and ComplexAssetAssetCategory eq 'Roads'
or	Or	$filter=CAOCI lt 2 or CAOCI gt 2

Note: To find records where an attribute is NULL, equate the attribute to 'null'.

The OData specification also outlines the syntax for Filter.

The following sample returns the first 10,000 asset fields listed in the select statement where CAOCI is not null:

https://[your_env].assetic.net/odata/assets?$top=10000&$select=ComplexAssetId,ComplexAssetAssetCategory,ComplexAssetName ,ComplexAssetLastModified,CAStatus,CAOCI&$filter=CAOCI%20ne%20null

Filtering by datetime requires a specific format.

Where a GMT offset is specified:

yyyy-mm-ddThh:mm:ss+HH:MM (The "+" should be escaped by "%2B".)
yyyy-mm-ddThh:mm:ss-HH:MM

otherwise:

yyyy-mm-ddThh:mm:ssZ

The datetime is not encapsulated with quotes because it is not a string. In the example below, work orders modified after 2017-11-21T15:20:35+09:30 are returned where:

21 November 2017 is the date
15:20:35 is the 24 hour time
+9:30 is the GMT offset

https://[your_env].assetic.net/odata/workorder?$select=WOLastModified&
$filter=WOLastModified%20ge%202017-11-21T15:20:35%2B09:30

Note: The datetime offset is not applied to the filter since the datetime offset indicates the local timezone, the date-time is unchanged.

Order By

The above search may be further refined by specifying ascending or descending order.

The data is sorted before it is returned by the OData endpoint. This is an important feature in situations where the number of results exceeds the Assetic OData result limit of 10,000. The sort is applied to all the records in Assetic (not just the first 10,000 results).

The keyword 'asc' may be used to define the order as 'ascending' or the alternate keyword 'desc' may be used to define the order as 'descending'. The keyword follows the field name

The OData specification outlines the syntax for Order By.

The following sample returns the first 10,000 asset fields listed in the select statement where CAOCI is not null, ordered by CAOCI in Descending order:

https://[your_env].assetic.net/odata/assets?$top=10000&$select=ComplexAssetId,ComplexAssetAssetCategory,ComplexAssetName, ComplexAssetLastModified,CAStatus,CAOCI &$filter=CAOCI%20ne%20null &$orderby=CAOCI desc

Note: For Microsoft Excel and Power BI the orderby can be defined in the preview grid when setting the record source by clicking on the column header of the field to order by. Exel and Power BI will then include the orderby in the OData query itself.

Supported OData Aggregation methods

The OData specification defines a set of functions that can be used to aggregate data. This is known as the Extension for Data Aggregation.

For all aggregation methods, the resultant aggregation value must be aliased using the 'as' keyword

Grouping

The simplest aggregation is to use the groupby transformation to request distinct value combinations of the specified fields. In this case there is no aggregation, so there is no need for an alias using the 'as' keyword.

The following sample returns the unique set of Functional Locations for the Roads category.

https://[your_env].assetic.net/odata/assets?$apply=groupby((GroupAssetIdL1)) &$filter=ComplexAssetAssetCategory%20eq%20%27Roads%27

Count Distinct

The aggregation method countdistinct counts the distinct values, omitting any null values.

The following sample returns the total number of assets in each asset category. Since Asset ID is unique and never null it ComplexAssetId the ideal candidate to count distinct.

https://[your_env].assetic.net/odata/assets? $apply=groupby((ComplexAssetAssetCategory), aggregate(ComplexAssetId%20with%20countdistinct%20as%20Count))

A filter may also be applied to the aggregation query.

The following sample returns the total number of assets in the Roads asset category

https://[your_env].assetic.net/odata/assets? $apply=groupby((ComplexAssetAssetCategory), aggregate(ComplexAssetId%20with%20countdistinct%20as%20Count)) &$filter=ComplexAssetAssetCategory%20eq%20%27Roads%27

Sum

A numeric field (metadata type=Edm.Decimal) may be summed using the sum method.

The following sample returns the total length of Sewer Pressure Pipes

https://[your_env].assetic.net/odata/assets? $apply=groupby((ComplexAssetAssetCategory), aggregate(PipeLength%20with%20sum%20as%20Total_Length)) &$filter=ComplexAssetAssetCategory%20eq%20%27Sewer%20Pressure%20Pipes%27

Average

A numeric field (metadata type=Edm.Decimal) may be averaged using the average method.

The following sample returns the average value for CAOCI (overall condition index) for each asset category

https://[your_env].assetic.net/odata/assets? $apply=groupby((ComplexAssetAssetCategory), aggregate(CAOCI%20with%20average%20as%20Average_OCI))

Max

The maximum value for a numeric field (metadata type=Edm.Decimal) may be returned using the max method.

The following sample returns the maximum OCI for each asset category

https://[your_env].assetic.net/odata/assets? $apply=groupby((ComplexAssetAssetCategory), aggregate(CAOCI%20with%20max%20as%20Max_OCI))

Min

The minimum value for a numeric field (metadata type=Edm.Decimal) may be returned using the min method.

The following sample returns the minimum OCI for each asset category

https://[your_env].assetic.net/odata/assets? $apply=groupby((ComplexAssetAssetCategory), aggregate(CAOCI%20with%20min%20as%20Min_OCI))

Further Reading

For more technical information about OData, refer to the odata.org website, and this link for more details about OData operators.

For more technical information about JSON, refer to the json.org website.